package com.example.sky20.dao;

import com.example.sky20.Bean.NoticeBean;
import com.example.sky20.util.DBConnection;
import com.example.sky20.util.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NoticeDao {
    DBConnection DBConn = new DBConnection();
    //todo 完成增删改查功能
    public List<NoticeBean> getList(int page) {
        Connection conn = DBConn.getConn();
        String sql = "select * from notice limit "+(page-1)*10+",10";
        List<NoticeBean> list = new ArrayList<>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                NoticeBean noticeBean = new NoticeBean();
                noticeBean.setNoticeId(rs.getInt("NoticeId"));
                noticeBean.setNoticeTitle(rs.getString("NoticeTitle"));
                noticeBean.setNoticeContent(rs.getString("NoticeContent"));
                noticeBean.setNoticeTime(Time.getDateTime());
                noticeBean.setAdminName(rs.getString("AdminName"));
                list.add(noticeBean);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * @param noticeBean 传入要更新的新闻对象,必须包含新闻id值
     * @return 收影响行数
     */
    public int update(NoticeBean noticeBean) {
        Connection conn = DBConn.getConn();
        if (noticeBean.getNoticeId() > 0) {
            try {
                Statement stmt = conn.createStatement();
                String sql = "update notice set NamesTitle='" + noticeBean.getNoticeTitle() + "'"
                        + ",NoticeContent+'" + noticeBean.getNoticeContent() + "'"
                        + ",NoticeTime='" +  Time.getDateTime()  + "'"//更新时使用当前系统时间
                        + ",AdminName='" + noticeBean.getAdminName()+ "'"//
                        +"where NoticeId='"+noticeBean.getNoticeId()+"'";
                return stmt.executeUpdate(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }
        return 0;
    }

    public int add(NoticeBean noticeBean) {
        Connection conn = DBConn.getConn();
        try {//使用预处理执行插入
            String sql = "insert into notice(NoticeTitle,NoticeContent,NoticeTime,AdminName) values(?,?,?,?)";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1,noticeBean.getNoticeTitle());//将第一个参数设置为标题内容
            pmst.setString(2,noticeBean.getNoticeContent());
            pmst.setString(3,(new Date()).toString());
            pmst.setString(4,"临时管理员");
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    /**
     * 删除功能
     */
    public int deleteNewsByNewsID(int noticeID) {
        Connection conn = DBConn.getConn();
        String sql = "delete from notice where NoticeId=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,noticeID);
            return pmst.executeUpdate();
        } catch (SQLException e) {

        }
        return 0;
    }
    public int countPage() {
        Connection conn = DBConn.getConn();
        String sql = "select count(*) from notice";
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);//获取查询结果集
            if (rs.next()) {
                return (rs.getInt(1) / 10) + 1;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return 0;
    }
    public NoticeBean getNoticeById(int noticeID) {
        Connection conn = DBConn.getConn();
        NoticeBean noticeBean = new NoticeBean();
        try {
            String sql = "select * from notice where NoticeID=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,noticeID);
            ResultSet rs = pmst.executeQuery();
            if (rs.next()){
                noticeBean.setNoticeID(rs.getInt(1));
                noticeBean.setNoticeTitle(rs.getString(2));
                noticeBean.setNoticeContent(rs.getString(3));
                noticeBean.setNoticeTime(rs.getString(4));
                noticeBean.setAdminName(rs.getString(5));
                return noticeBean;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }
}